package it.koutifaris.travianmap.dal.sqlservices.revision;

import it.koutifaris.travianmap.dal.RevisionService;
import it.koutifaris.travianmap.dal.sqlservices.MapSqlContract.Revisions;
import it.koutifaris.travianmap.utils.DayTruncUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class SqlRevisionService implements RevisionService {

	private final SQLiteDatabase db;

	public SqlRevisionService(SQLiteDatabase db) {
		this.db = db;
	}

	@Override
	public boolean revisionExist(Revision revision) {
		String[] columns = { Revisions.COLUMN_NAME_REVISION_ID, Revisions.COLUMN_NAME_DATE };

		String whereCondition = Revisions.COLUMN_NAME_REVISION_ID + "= ? " +
				"AND " + Revisions.COLUMN_NAME_DATE + " = ?";

		String[] whereArgs = {
				String.valueOf(revision.getId()),
				String.valueOf(truncDateToDay(revision.getDay()).getTime())
		};

		Cursor cursor = db.query(
				Revisions.TABLE_NAME,
				columns,
				whereCondition,
				whereArgs,
				null, null,
				Revisions.COLUMN_NAME_REVISION_ID + " DESC");

		boolean result = (cursor.getCount() >= 1);
		cursor.close();

		return result;
	}

	@Override
	public Revision newRevisionForDay(Date date) {
		Date day = truncDateToDay(date);
		return new RevisionImpl(day.getTime(), day);
	}

	@Override
	public Revision getLatestRevision() {
		String maxDateInnerQuery = "SELECT "
				+ "MAX(" + Revisions.COLUMN_NAME_DATE + ") AS " + Revisions.COLUMN_NAME_DATE
				+ " FROM " + Revisions.TABLE_NAME;

		String query = "SELECT "
				+ Revisions.TABLE_NAME + "." + Revisions.COLUMN_NAME_REVISION_ID + ", "
				+ Revisions.TABLE_NAME + "." + Revisions.COLUMN_NAME_DATE
				+ " FROM "
				+ Revisions.TABLE_NAME + " INNER JOIN (" + maxDateInnerQuery + ") as maxQuery "
				+ " ON (" + Revisions.TABLE_NAME + "." + Revisions.COLUMN_NAME_DATE
				+ " = maxQuery." + Revisions.COLUMN_NAME_DATE + ")";

		Cursor cursor = db.rawQuery(query, null);
		return getRevisionFromCursorAndClose(cursor);
	}

	private Revision getRevisionFromCursorAndClose(Cursor cursor) {
		cursor.moveToNext();
		Revision result = getRevisionFromCursor(cursor);
		cursor.close();

		return result;
	}

	@Override
	public Revision findRevisionForDay(Date day) {
		List<Revision> revisions = listRevisions(day, day);

		Revision result = null;
		if (!revisions.isEmpty()) {
			result = revisions.get(0);
		}

		return result;
	}

	@Override
	public List<Revision> listAllRevisions() {
		String[] columns = { Revisions.COLUMN_NAME_REVISION_ID, Revisions.COLUMN_NAME_DATE };

		Cursor cursor = db.query(Revisions.TABLE_NAME, columns, null, null, null, null,
				Revisions.COLUMN_NAME_REVISION_ID + " DESC");

		return getRevisionsFromCursor(cursor);
	}

	@Override
	public List<Revision> listRevisions(Date from, Date to) {
		String[] columns = { Revisions.COLUMN_NAME_REVISION_ID, Revisions.COLUMN_NAME_DATE };

		StringBuilder whereCondition = new StringBuilder();
		whereCondition
				.append(Revisions.COLUMN_NAME_DATE)
				.append(" BETWEEN ").append(truncDateToDay(from).getTime())
				.append(" AND ").append(truncDateToDay(to).getTime());

		Cursor cursor = db.query(
				Revisions.TABLE_NAME,
				columns,
				whereCondition.toString(),
				null, null, null,
				Revisions.COLUMN_NAME_REVISION_ID + " DESC");

		return getRevisionsFromCursor(cursor);
	}

	private List<Revision> getRevisionsFromCursor(Cursor cursor) {
		List<Revision> revisions = new ArrayList<Revision>();

		while (cursor.moveToNext()) {
			revisions.add(getRevisionFromCursor(cursor));
		}

		cursor.close();

		return revisions;
	}

	private Revision getRevisionFromCursor(Cursor cursor) {
		if (cursor.isAfterLast()) {
			return NullRevision.nullRevision();
		}

		long revisionId = cursor.getLong(cursor.getColumnIndex(Revisions.COLUMN_NAME_REVISION_ID));
		Date revisionDate = new Date(cursor.getLong(cursor.getColumnIndex(Revisions.COLUMN_NAME_DATE)));

		return new RevisionImpl(revisionId, revisionDate);
	}

	private Date truncDateToDay(Date date) {
		return DayTruncUtils.truncDateToDay(date);
	}

	@Override
	public void close() {
		db.close();
	}
}
